<?php

class WorkDAO {
    
    public static function startWork($work) {
        $db = Database::getInstance();
        $db->query("INSERT INTO works(uid, pid, ptid) VALUES(
            " . $work->getUid() . ",
            " . $work->getPid() . ",
            " . $work->getPtid() . "
        )");
        return $db->insertId();
    }
    
    public static function getCurrentWork($user) {
        $db = Database::getInstance();
        $db->query("SELECT * FROM works WHERE uid=" . $user->getUid() . " AND ended=0");
        if($db->getNumRows() > 0) {
            return $db->dbToObjectKeys($db->getRowAssoc());
        } else {
            return array();
        }
    }
    
    public static function endWork($work) {
        $db = Database::getInstance();
        $db->query("UPDATE works SET time_end='" . $work->getTimeEnd() . "', ended=1 WHERE wid=" . $work->getWid());
    }
    
    public static function getWorkDone($startDate,$endDate, $project=false, $user=false) {
        $db = Database::getInstance();
        if($project !== false) {
            if($user !== false) {
                $db->query("SELECT
                                *
                            FROM
                                works
                            WHERE
                                uid=" . $user->getUid() . " AND
                                pid=" . $project->getPid() . " AND
                                time_start >= FROM_UNIXTIME($startDate) AND
                                time_end <= FROM_UNIXTIME($endDate) AND
                                ended = 1");
            } else {
                $db->query("SELECT
                                *
                            FROM
                                works
                            WHERE
                                pid=" . $project->getPid() . " AND
                                time_start >= FROM_UNIXTIME($startDate) AND
                                time_end <= FROM_UNIXTIME($endDate) AND
                                ended = 1");
            }
        } else {
            if($user !== false) {
                $db->query("SELECT
                                *
                            FROM
                                works
                            WHERE
                                uid=" . $user->getUid() . " AND
                                time_start >= FROM_UNIXTIME($startDate) AND
                                time_end <= FROM_UNIXTIME($endDate) AND
                                ended = 1");
            } else {
                $db->query("SELECT
                                *
                            FROM
                                works
                            WHERE
                                time_start >= FROM_UNIXTIME($startDate) AND
                                time_end <= FROM_UNIXTIME($endDate) AND
                                ended = 1");
            }
        }
        return $db->getObjectArray();
    }
    
    public static function getHours($startDate, $endDate, $user, $which=0) { //0 all 1 normal(pn-pt) 2 weekends 3 trips
        $db = Database::getInstance();
        switch($which) {
            case 0:
                $db->query("SELECT
                        SUM(time_end-time_start)
                    FROM
                        works
                    WHERE
                        time_start >= FROM_UNIXTIME($startDate) AND
                        time_end <= FROM_UNIXTIME($endDate) AND
                        ended = 1 AND
                        uid = " . $user->getUid());
            break;
            case 1:
                $db->query("SELECT
                        SUM(time_end-time_start)
                    FROM
                        works
                    WHERE
                        time_start >= FROM_UNIXTIME($startDate) AND
                        time_end <= FROM_UNIXTIME($endDate) AND
                        WEEKDAY(time_start) >= 0 AND
                        WEEKDAY(time_start) <= 4 AND
                        ended = 1 AND
                        uid = " . $user->getUid());
            break;
            case 2:
                $db->query("SELECT
                        SUM(time_end-time_start)
                    FROM
                        works
                    WHERE
                        time_start >= FROM_UNIXTIME($startDate) AND
                        time_end <= FROM_UNIXTIME($endDate) AND
                        WEEKDAY(time_start) >= 5 AND
                        ended = 1 AND
                        uid = " . $user->getUid());                
            break;
            case 3:
                $db->query("SELECT
                                SUM(hours_worked)*3600
                            FROM
                                work_trips
                            WHERE
                                `date` >= FROM_UNIXTIME($startDate) AND
                                `date` <= FROM_UNIXTIME($endDate) AND
                                uid = " . $user->getUid());
            break;
        }
        return round($db->getOne()/3600,2);
    }
    
    public static function getHoursByProjects($startDate, $endDate, $user) {
        $db = Database::getInstance();
        $db->query("SELECT
                        SUM(time_end-time_start) as hours, pid
                    FROM
                        works
                    WHERE
                        time_start >= FROM_UNIXTIME($startDate) AND
                        time_end <= FROM_UNIXTIME($endDate) AND
                        ended = 1
                    GROUP BY
                        pid");
        $hours = array();
        while($row = $db->getRowAssoc()) {
            $row['hours'] = round($row['hours']/3600,2);
            $hours[] = $row;
        }
        return $hours;
    }
    
    public static function getHoursByTasks($startDate, $endDate, $user) {
        $db = Database::getInstance();
        $db->query("SELECT
                        SUM(time_end-time_start) as hours, ptid
                    FROM
                        works
                    WHERE
                        time_start >= FROM_UNIXTIME($startDate) AND
                        time_end <= FROM_UNIXTIME($endDate) AND
                        ended = 1
                    GROUP BY
                        ptid");
        $hours = array();
        while($row = $db->getRowAssoc()) {
            $row['hours'] = round($row['hours']/3600,2);
            $hours[] = $row;
        }
        return $hours;
    }
    
    public static function getTaskWork($task) {
        $db = Database::getInstance();
        $db->query("SELECT * FROM works WHERE ptid=" . $task->getPtid());
        return $db->getObjectArray();
    }
    
    public static function hasTasksAtProgress($user) {
        $db = Database::getInstance();
        $db->query("SELECT * FROM works WHERE uid=" . $user->getUid() . " AND ended=0");
        return $db->getNumRows() > 0;
    }
    
    public static function getTripsInfo($startDate, $endDate, $user) {
        $db = Database::getInstance();
        $db->query("SELECT
                        `date`,
                        hours_worked
                    FROM
                        work_trips
                    WHERE
                        uid=" . $user->getUid() . " AND 
                        `date` >= FROM_UNIXTIME($startDate) AND
                        `date` <= FROM_UNIXTIME($endDate)
                ");
        if($db->getNumRows() > 0) {
            return $db->getObjectArray();
        } else {
            return array();
        }
    }
}

?>
